import sys
import os

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))

from config import result_bucket
from aws_model.athena_submit import submit
from sql_lambda.delete import delete_data
from base_class.inspect_logging import print_to_log
import datetime
import json
from aws_model.aws_stepfunc import send_task_success, send_task_failure


def result_ljbll(event):
    date = event.get('p_date')
    if not date:
        date = datetime.datetime.today().strftime('%Y-%m-%d')
    table_name = sys._getframe().f_code.co_name
    delete_data(result_bucket,table_name, date)
    sql = f'''insert into fridge_biz_result.result_ljbll
            (
            select
                a.*
            from
                (
                select
                       factory_time as factory_date,
                       wgsj as complete_date,
                       product_type as product_type,
                       gzdl as fault_class1,
                       model,
                       gzxx as fault_class2,
                       count(distinct objectid) as fault_count,
                       substr(factory_time,1,4) as factory_year,
                       substr(wgsj,1,4) as complete_year,
                       format_datetime(date('{date}') - interval '1' month, 'YYYY-MM') as time
                FROM
                    (
                        SELECT
                            upper (if(t3.model is null, regexp_extract(t1.zzfld000036, '[a-zA-Z0-9]+-[a-zA-Z0-9]+'), t3.model)) as model,
                            objectid,
                            case when zzfld000001='QT30' then '冰箱'
                                else '冰柜' end as product_type,
                            zzfld00000b sn,
                            if(zzfld00002gt is null,'',zzfld00002gt) gzxx,
                            if(gzdl is null,'',gzdl) as gzdl,
                            concat(substr(cast (date9 as varchar), 1, 4), '-', substr(cast (date9 as varchar), 5, 2)) as wgsj,
                            concat(
                                t2.name, '-',
                                substr(t1.zzfld00000b, 17, 2)
                            ) as factory_time
                        from
                            ods.kyd_ods t1
                        left join
                            dim.tree_book t2
                        on
                            substr(t1.zzfld00000b, 16, 1) = t2.code and t2.classify_name = 'sn出厂年份'
                        left join
                            dim.material t3
                        on
                            substr(t1.zzfld00000b, 4, 7) = t3.material and t3.type_code =  'FRIDGE'
                        where
                            length (zzfld00000b) = 24
                        and
                            zzfld000001 in ('QT30', 'QT20')
                        and
                            gzdl is not null and gzdl != ''
                        and
                            p_date < '{date}'
                    ) t
                where
                    factory_time is not null and wgsj is not null
                group by
                    factory_time, wgsj, product_type, gzdl, model, gzxx
                ) a
            left join
                (
                    select
                        *
                    from
                        (
                        select
                           *,row_number() over(partition by factory_date,complete_date,product_type,fault_class1,model,fault_class2 order by time desc) as rn
                        from
                            fridge_biz_result.result_ljbll
                        where
                            time < format_datetime(date('{date}') - interval '1' month, 'YYYY-MM')
                        ) t
                    where rn = 1
                ) b
            on
                a.factory_date = b.factory_date and a.complete_date = b.complete_date and a.product_type = b.product_type
                and a.fault_class1 = b.fault_class1 and a.model = b.model and a.fault_class2 = b.fault_class2
            where
                b.factory_date is null or (a.fault_count != b.fault_count)
            )'''

    athena_response = submit(sql)
    print_to_log("athena_response: ", athena_response)
    output = {"msg": f"{table_name} submit ok", "Payload": {}}
    output = json.dumps(output)
    step_response = send_task_success(tasktoken, output)
    print_to_log("step_response: ", step_response)


if __name__ == '__main__':
    try:
        event = json.loads(sys.argv[1])
        tasktoken = event.get('TaskToken')
        result_ljbll(event)
    except Exception as e:
        print_to_log("athena_submit 失败")
        print_to_log("原因： ", str(e))
        send_task_failure(str(e), f'执行{sys._getframe().f_code.co_filename}错误', tasktoken)

